package org.wwy.demo.excel.test.graph;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.wwy.demo.excel.graph.GraphInstance;

import java.awt.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

public class MyExcleChart {

    private static Color titleBackColor = new Color(155, 194, 230);    //表头背景色

    public static void doWork(List<String> title, List<String> styleList, Map<String, List<Object>> day2ColValueList, File file,
                              String sheetName, XSSFWorkbook wb, int dateSize) throws IOException {
        OutputStream out = null;
        try {
            int sheetIndex = wb.getSheetIndex(sheetName);
            if (sheetIndex >= 0) {
                wb.removeSheetAt(sheetIndex);
            }
            int sheetNum = wb.getNumberOfSheets();
            XSSFSheet sheet = wb.createSheet();
            wb.setSheetName(sheetNum, sheetName);
            out = new FileOutputStream(file);

            //设置内容样式
            XSSFCellStyle style = setBorder(wb);

            //设置表头字体
            XSSFFont font = wb.createFont();
            font.setBold(true);    //加粗
            //设置表头样式
            XSSFCellStyle headStyle = setBorder(wb);
            headStyle.setFillForegroundColor(new XSSFColor(titleBackColor));
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setFont(font);

            //隐藏列
            hiddenColumn(sheet, sheetName);

            Row row;
            Cell cell = null;
            row = sheet.createRow(0);
            //写入表头
            int titleColIndex = 0;
            for (String t : title) {
                cell = row.createCell((short) titleColIndex);
                cell.setCellValue(t);
                cell.setCellStyle(headStyle);
                titleColIndex++;
            }
            //写入数据
            int rowIndex = 1;
            for (String key : day2ColValueList.keySet()) {
                row = sheet.createRow(rowIndex);

                List<Object> dataList = day2ColValueList.get(key);
                cell = row.createCell(0);
                cell.setCellStyle(style);
                cell.setCellValue(rowIndex);

                int cellIndex = 1;
                for (Object s : dataList) {
                    //填充单元格
                    String cellstyle = styleList.get(dataList.indexOf(s) + 1);
                    cell = row.createCell(cellIndex);
                    cell.setCellStyle(style);
                    //此处可以对特殊的行进行处理
                    if ("speciaRowName".equals(key) && cellIndex > 9) {
                        cell = row.createCell(0);
                        cell.setCellStyle(style);
                        cell.setCellValue("speciaRowName");
                        cell = row.createCell(1);
                        cell.setCellStyle(style);
                        cell.setCellValue("");
                        cell = row.createCell(2);
                        cell.setCellStyle(style);
                        cell.setCellValue("");
                        cell = row.createCell(3);
                        cell.setCellStyle(style);
                        cell.setCellValue("");
                        cell = row.createCell(4);
                        cell.setCellStyle(style);
                        cell.setCellValue("");
                        cell = row.createCell(cellIndex);
                        cell.setCellStyle(style);
                        double dble = (double) s;
                        cell.setCellValue(dble);
                    } else if ("int".equals(cellstyle) && null != s) {
                        int num = (int) s;
                        cell.setCellValue(num);
                    } else if ("double".equals(cellstyle) && null != s) {
                        double dble = (double) s;
                        cell.setCellValue(dble);
                    } else {
                        cell.setCellValue(null == s ? "" : (String) s);
                    }
                    cellIndex++;
                }
                rowIndex++;
            }
            //绘制图表
            if (day2ColValueList.size() > 0) {
                drawChart(sheet, day2ColValueList, dateSize);
            }
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static void drawChart(XSSFSheet sheet, Map<String, List<Object>> day2ColValueList, int dateNum) {

        // 业务汇总表，需要统计身份核查、高清人像、银行卡、手机实名四个业务的折线图
        int i = 0;
        CellRangeAddress lineRange = new CellRangeAddress(6, 23, 0, 7);
        int numstartcol = 5;
        int numendcol = 5 + dateNum - 1;
        CellRangeAddress titleRange = new CellRangeAddress(0, 0, numstartcol, numendcol);
        GraphInstance instance = new GraphInstance(sheet, lineRange,"aaaa");
        for (String key : day2ColValueList.keySet()) {
            // 折线图x轴单位起止列（numstartcol，numendcol），以及计费笔数数据所在行列
            int numstartrow = 1 + i;
            int numendrow = 1 + i;

            CellRangeAddress dataRange = new CellRangeAddress(numstartrow, numendrow, numstartcol, numendcol);
            instance.drawLineChart(key, dataRange);
            i++;
        }
    }


    /**
     * 隐藏列
     *
     * @param sheet
     * @param sheetName
     */
    private static void hiddenColumn(XSSFSheet sheet, String sheetName) {
		/*if(sheetName.equals("sheetname2")){//需要隐藏列
			sheet.setColumnHidden(3, true);
			sheet.setColumnHidden(4, true);
			sheet.setColumnHidden(5, true);
			sheet.setColumnHidden(6, true);
			sheet.setColumnHidden(7, true);
			sheet.setColumnHidden(8, true);
		}*/
    }

    /**
     * 设置边框
     */
    private static XSSFCellStyle setBorder(XSSFWorkbook wb) {
        XSSFCellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);    //边框
        style.setBorderTop(BorderStyle.THIN);        //边框
        style.setBorderLeft(BorderStyle.THIN);        //边框
        style.setBorderRight(BorderStyle.THIN);        //边框
        return style;
    }

}
